package com.lntea.jdbc.spring;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringRunner;

import com.lntea.jdbc.spring.bean.GoodsStock;

@RunWith(SpringRunner.class)
@ContextConfiguration(locations={"classpath:com/lntea/jdbc/spring/jdbc-template.xml"})
public class BatchOperationTest {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	
	// 使用JdbcTemplate和BatchPreparedStatementSetter
	@Test
	public void testJdbcTemplateWithBatchPreparedStatementSetter(){
		final List<GoodsStock> gsList = new ArrayList<GoodsStock>();
		gsList.add(new GoodsStock(1, 20.1));
		gsList.add(new GoodsStock(2, 18.6));
		
		String sql = "update t_wms_goods_stock set amount = ? where id = ?";
		int[] updateCounts = jdbcTemplate.batchUpdate(sql, 
				new BatchPreparedStatementSetter() {
					
					public void setValues(PreparedStatement ps, int i) throws SQLException {
						ps.setDouble(1, gsList.get(i).getAmount());
						ps.setInt(2, gsList.get(i).getId());
					}
					
					public int getBatchSize() {
						return gsList.size();
					}
				});
		System.out.println(updateCounts[0] + "," + updateCounts[1]);
	}
	
	// 使用NamedParameterJdbcTemplate
	@Test
	public void testNamedParameterJdbcTemplate(){
		final List<GoodsStock> gsList = new ArrayList<GoodsStock>();
		gsList.add(new GoodsStock(1, 20.1));
		gsList.add(new GoodsStock(2, 18.6));
		
		String sql = "update t_wms_goods_stock set amount = :amount where id = :id";
		SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(gsList.toArray());
		int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(sql, batch);
		System.out.println(updateCounts[0] + "," + updateCounts[1]);
	}
	
	
	// 使用JdbcTemplate和List<Object[]>
	@Test
	public void testJdbcTemplateWithList(){
		final List<GoodsStock> gsList = new ArrayList<GoodsStock>();
		gsList.add(new GoodsStock(1, 20.1));
		gsList.add(new GoodsStock(2, 18.6));
		
		List<Object[]> batch = new ArrayList<Object[]>();
		gsList.stream().forEach(g -> batch.add(new Object[]{g.getAmount(), g.getId()}));
		
		String sql = "update t_wms_goods_stock set amount = ? where id = ?";
		int[] updateCounts = jdbcTemplate.batchUpdate(sql, batch);
		System.out.println(updateCounts[0] + "," + updateCounts[1]);
	}
	
	// 使用JdbcTemplate和ParameterizedPreparedStatementSetter
	// 支持将大批量的update拆分成多个小批量执行
	@Test
	public void testParameterizedPreparedStatementSetter(){
		final List<GoodsStock> gsList = new ArrayList<GoodsStock>();
		gsList.add(new GoodsStock(1, 20.1));
		gsList.add(new GoodsStock(2, 18.6));
		
		String sql = "update t_wms_goods_stock set amount = ? where id = ?";
		int[][] updateCounts = jdbcTemplate.batchUpdate(sql, 
				gsList, 
				1, 
				new ParameterizedPreparedStatementSetter<GoodsStock>() {

					@Override
					public void setValues(PreparedStatement ps, GoodsStock argument) throws SQLException {
						ps.setDouble(1, argument.getAmount());
						ps.setInt(2, argument.getId());
					}
				});
		System.out.println(updateCounts[0][0] + "," + updateCounts[1][0]);
	}
	
}
